package com.zyh.homework.test1;

import com.zyh.domain.Stu;
import com.zyh.utils.JDBCUtil;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @Author zyh
 * @Date 2024/8/22 15:58
 * @Description
 */

public class Test1 {
    @Test
    public void test1() throws SQLException {
        // 1. 创建连接
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        // 创建 sql 语句
        String sql = "select * from student";
        // 使用 statement 执行 sql 语句
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            double weight = rs.getDouble("weight");
            String address = rs.getString("address");
            int classID = rs.getInt("classID");
            System.out.println(sid + "--" + sname + "--" + age + "--" + weight + "--" + address + "--" + classID);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    @Test
    public void test2() throws SQLException {
        Scanner input = new Scanner(System.in);
        System.out.println("请输入当前页码：");
        String pageNum = input.nextLine();
        System.out.println("请输入每页条数：");
        String pageCount = input.nextLine();
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from student limit" + (Integer.parseInt(pageNum) - 1) * Integer.parseInt(pageCount) + "," + pageCount;
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            double weight = rs.getDouble("weight");
            String address = rs.getString("address");
            int classID = rs.getInt("classID");
            System.out.println(sid + "--" + sname + "--" + age + "--" + weight + "--" + address + "--" + classID);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    public static void main(String[] args) throws SQLException {
        Scanner input = new Scanner(System.in);
        System.out.println("请输入当前页码：");
        String pageNum = input.nextLine();
        System.out.println("请输入每页条数：");
        String pageCount = input.nextLine();
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from student limit " + (Integer.parseInt(pageNum) - 1) * Integer.parseInt(pageCount) + " , " + Integer.parseInt(pageCount);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            double weight = rs.getDouble("weight");
            String address = rs.getString("address");
            int classID = rs.getInt("classID");
            System.out.println(sid + "--" + sname + "--" + age + "--" + weight + "--" + address + "--" + classID);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    @Test
    public void test3() throws Exception {
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from student order by age asc";
        ResultSet rs = statement.executeQuery(sql);
        // 创建一个 list 集合
        List<Stu> list = new ArrayList<>();
        while (rs.next()) {
            int sid = rs.getInt("sid");
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            double weight = rs.getDouble("weight");
            String address = rs.getString("address");
            int classID = rs.getInt("classID");
            list.add(new Stu(sid, sname, age, weight, address, classID));
            System.out.println(sid + "--" + sname + "--" + age + "--" + weight + "--" + address + "--" + classID);
        }
        System.out.println("-----------------------");
        list.stream().sorted((item1, item2) -> item1.getAge() > item2.getAge() ? -1 : (item1.getAge() < item2.getAge() ? 1 : 0)).forEach(System.out::println);

        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    @Test
    public void test4() throws Exception {
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select student.sname,student.age,classroom.className from student join classroom on student.classID=classroom.classID";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            String className = rs.getString("className");
            System.out.println(sname + "--" + age + "--" + className);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    @Test
    public void test5() throws Exception {
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="select t2.className, count(*) as num from student t1 join classroom t2 on t1.classID=t2.classID group by t2.classID";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            String className = rs.getString("className");
            int num = rs.getInt("num");
            System.out.println(className+"--"+num);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }
}
